import { NextRequest, NextResponse } from 'next/server';
import { getDeviceParameterDb } from '@/lib/device-parameter-database';

// 🔥 新增：更新主实例表嵌入字段的函数
async function updateMainInstanceTableEmbeddedFields(deviceParameterDb: any, instanceId: string, tableName: string, data: any) {
  console.log('🔥 [UPDATE] === 开始更新主实例表嵌入字段 ===');
  console.log('🔥 [UPDATE] instanceId:', instanceId);
  console.log('🔥 [UPDATE] tableName:', tableName);
  console.log('🔥 [UPDATE] data:', JSON.stringify(data, null, 2));

  // 1. 根据实例ID查找主实例表
  const tablesStmt = deviceParameterDb.db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
  `);
  const tables = tablesStmt.all();
  
  let mainInstanceTable = null;
  for (const table of tables) {
    try {
      const checkStmt = deviceParameterDb.db.prepare(`
        SELECT id FROM [${table.name}] WHERE id = ?
      `);
      const instance = checkStmt.get(parseInt(instanceId));
      if (instance) {
        mainInstanceTable = table.name;
        break;
      }
    } catch (error) {
      // 继续检查下一个表
      continue;
    }
  }
  
  if (!mainInstanceTable) {
    throw new Error(`未找到实例ID ${instanceId} 对应的主实例表`);
  }
  
  console.log('🔥 [UPDATE] 找到主实例表:', mainInstanceTable);
  
  // 2. 确定字段前缀
  let prefix = '';
  if (tableName.includes('table_c2') || tableName.includes('naming')) prefix = 'naming';
  else if (tableName.includes('table_c6') || tableName.includes('position')) prefix = 'position';
  else if (tableName.includes('table_c494') || tableName.includes('base_params')) prefix = 'base_params';
  else {
    // 自动检测前缀
    if (tableName.includes('c2')) prefix = 'naming';
    else if (tableName.includes('c6')) prefix = 'position';
    else if (tableName.includes('c494')) prefix = 'base_params';
    else {
      console.log('⚠️ [UPDATE] 无法确定字段前缀，跳过更新');
      return;
    }
  }
  
  console.log('🔥 [UPDATE] 使用字段前缀:', prefix);
  
  // 3. 构建嵌入字段的更新SQL
  const embeddedUpdates = [];
  const updateValues = [];
  
  Object.entries(data).forEach(([fieldName, value]) => {
    if (value !== null && value !== undefined && String(value).trim() !== '') {
      const embeddedFieldName = `${prefix}_${fieldName}`;
      embeddedUpdates.push(`[${embeddedFieldName}] = ?`);
      updateValues.push(String(value).trim());
      console.log('🔥 [UPDATE] 准备更新字段:', embeddedFieldName, '=', value);
    }
  });
  
  if (embeddedUpdates.length === 0) {
    console.log('⚠️ [UPDATE] 没有需要更新的字段');
    return;
  }
  
  // 4. 执行更新
  const updateSQL = `
    UPDATE [${mainInstanceTable}] 
    SET ${embeddedUpdates.join(', ')}, updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `;
  updateValues.push(parseInt(instanceId));
  
  console.log('🔥 [UPDATE] 执行SQL:', updateSQL);
  console.log('🔥 [UPDATE] 参数值:', updateValues);
  
  const updateStmt = deviceParameterDb.db.prepare(updateSQL);
  const result = updateStmt.run(...updateValues);
  
  console.log('✅ [UPDATE] 嵌入字段更新成功:', {
    changes: result.changes,
    updatedFields: embeddedUpdates.length
  });
}

// GET /api/device-instance-parameters?instanceId=1&tableName=table_c2
export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);
    const instanceId = searchParams.get('instanceId');
    const tableName = searchParams.get('tableName');

    if (!instanceId || !tableName) {
      return NextResponse.json({
        success: false,
        error: '缺少设备实例ID或表名参数'
      }, { status: 400 });
    }

    const db = getDeviceParameterDb();
    const deviceParameterDb = db as any;

    console.log('=== 设备实例参数API调试 ===');
    console.log('请求参数:', { instanceId, tableName });

    // 动态查找设备实例表
    const findInstanceStmt = deviceParameterDb.db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name LIKE 'device_instances_%'
    `);
    const instanceTables = findInstanceStmt.all();
    console.log('找到的设备实例表:', instanceTables);

    let instance = null;
    let actualInstanceTableName = '';

    // 在所有设备实例表中查找
    for (const table of instanceTables) {
      try {
        const instanceStmt = deviceParameterDb.db.prepare(`
          SELECT * FROM ${table.name} WHERE id = ?
        `);
        const foundInstance = instanceStmt.get(parseInt(instanceId));
        if (foundInstance) {
          instance = foundInstance;
          actualInstanceTableName = table.name;
          console.log('在表中找到设备实例:', table.name, foundInstance);
          break;
        }
      } catch (error) {
        console.log('查询表失败:', table.name, error);
        continue;
      }
    }

    if (!instance) {
      return NextResponse.json({
        success: false,
        error: '设备实例不存在'
      }, { status: 404 });
    }

    // 获取设备类型映射信息
    const mapping = db.getDeviceTypeMapping(instance.device_type_code);
    if (!mapping) {
      return NextResponse.json({
        success: false,
        error: '设备类型映射不存在'
      }, { status: 404 });
    }

    // 获取字段元数据
    const fieldMetadata = db.getParameterTableFields(tableName);
    const tableMetadata = db.getTableMetadata(tableName);

    // 检查设备实例是否有参数数据
    // 为设备实例创建专门的参数表名
    const instanceParamTableName = `instance_params_${instanceId}_${tableName}`;
    
    // 检查实例参数表是否存在
    const checkTableStmt = deviceParameterDb.db.prepare(`
      SELECT name FROM sqlite_master WHERE type='table' AND name=?
    `);
    const paramTableExists = checkTableStmt.get(instanceParamTableName);

    let parameterData = {};
    if (paramTableExists) {
      // 获取现有参数数据
      const paramStmt = deviceParameterDb.db.prepare(`
        SELECT * FROM ${instanceParamTableName} ORDER BY created_at DESC LIMIT 1
      `);
      const paramRecord = paramStmt.get();
      if (paramRecord) {
        parameterData = paramRecord;
      }
    }

    return NextResponse.json({
      success: true,
      data: {
        instance,
        mapping,
        parameters: parameterData,
        fields: fieldMetadata,
        tableMetadata
      }
    });

  } catch (error) {
    console.error('获取设备实例参数失败:', error);
    return NextResponse.json({
      success: false,
      error: error instanceof Error ? error.message : '获取设备实例参数失败'
    }, { status: 500 });
  }
}

// POST /api/device-instance-parameters
export async function POST(request: NextRequest) {
  try {
    const body = await request.json();
    const { instanceId, tableName, data } = body;

    if (!instanceId || !tableName || !data) {
      return NextResponse.json({
        success: false,
        error: '缺少必要参数'
      }, { status: 400 });
    }

    const db = getDeviceParameterDb();
    const deviceParameterDb = db as any;

    console.log('=== 保存设备实例参数API调试 ===');
    console.log('请求参数:', { instanceId, tableName, data });

    // 动态查找设备实例表
    const findInstanceStmt = deviceParameterDb.db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name LIKE 'device_instances_%'
    `);
    const instanceTables = findInstanceStmt.all();

    let instance = null;
    let actualInstanceTableName = '';

    // 在所有设备实例表中查找
    for (const table of instanceTables) {
      try {
        const instanceStmt = deviceParameterDb.db.prepare(`
          SELECT * FROM ${table.name} WHERE id = ?
        `);
        const foundInstance = instanceStmt.get(parseInt(instanceId));
        if (foundInstance) {
          instance = foundInstance;
          actualInstanceTableName = table.name;
          break;
        }
      } catch (error) {
        continue;
      }
    }

    if (!instance) {
      return NextResponse.json({
        success: false,
        error: '设备实例不存在'
      }, { status: 404 });
    }

    // 获取字段元数据
    const fieldMetadata = db.getParameterTableFields(tableName);
    const fieldNames = fieldMetadata.map(f => f.field_name);

    // 为设备实例创建专门的参数表
    const instanceParamTableName = `instance_params_${instanceId}_${tableName}`;
    
    // 检查表是否存在，如果不存在则创建
    const checkTableStmt = deviceParameterDb.db.prepare(`
      SELECT name FROM sqlite_master WHERE type='table' AND name=?
    `);
    const tableExists = checkTableStmt.get(instanceParamTableName);

    if (!tableExists) {
      // 创建参数表
      const columnDefs = fieldNames.map(name => `\`${name}\` TEXT`).join(', ');
      const createTableSQL = `
        CREATE TABLE ${instanceParamTableName} (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          instance_id INTEGER NOT NULL,
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          ${columnDefs}
        )
      `;
      deviceParameterDb.db.prepare(createTableSQL).run();
    }

    const now = new Date().toISOString();

    // 检查是否已有记录
    const existingStmt = deviceParameterDb.db.prepare(`
      SELECT id FROM ${instanceParamTableName} WHERE instance_id = ?
    `);
    const existingRecord = existingStmt.get(parseInt(instanceId));

    if (existingRecord) {
      // 更新现有记录
      const setClause = fieldNames.map(name => `\`${name}\` = ?`).join(', ');
      const updateSQL = `
        UPDATE ${instanceParamTableName} 
        SET ${setClause}, updated_at = ?
        WHERE instance_id = ?
      `;
      const values = [...fieldNames.map(name => data[name] || null), now, parseInt(instanceId)];
      deviceParameterDb.db.prepare(updateSQL).run(...values);
    } else {
      // 创建新记录
      const columns = ['instance_id', 'created_at', 'updated_at', ...fieldNames.map(name => `\`${name}\``)];
      const placeholders = columns.map(() => '?').join(', ');
      const values = [parseInt(instanceId), now, now, ...fieldNames.map(name => data[name] || null)];

      const insertSQL = `
        INSERT INTO ${instanceParamTableName} (${columns.join(', ')})
        VALUES (${placeholders})
      `;
      deviceParameterDb.db.prepare(insertSQL).run(...values);
    }

    // 获取保存后的数据
    const savedStmt = deviceParameterDb.db.prepare(`
      SELECT * FROM ${instanceParamTableName} WHERE instance_id = ?
    `);
    const savedRecord = savedStmt.get(parseInt(instanceId));

    // 🔥 新增：同时更新主实例表的嵌入字段
    try {
      await updateMainInstanceTableEmbeddedFields(deviceParameterDb, instanceId, tableName, data);
    } catch (embeddedFieldError) {
      console.error('更新主实例表嵌入字段失败:', embeddedFieldError);
      // 不影响主要的参数保存功能，只记录错误
    }

    return NextResponse.json({
      success: true,
      data: {
        parameters: savedRecord
      }
    });

  } catch (error) {
    console.error('保存设备实例参数失败:', error);
    return NextResponse.json({
      success: false,
      error: error instanceof Error ? error.message : '保存设备实例参数失败'
    }, { status: 500 });
  }
}